IF OBJECT_ID('dbo.RptCSCRExecutedAgreements') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.RptCSCRExecutedAgreements
    IF OBJECT_ID('dbo.RptCSCRExecutedAgreements') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.RptCSCRExecutedAgreements >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.RptCSCRExecutedAgreements >>>'
END
go
-- =======================================================================            
-- Procedure Name: dbo.[RptCSCRExecutedAgreements]            
--            
-- Author:      Ponraja Chandran            
--            
-- Create date: 08/13/2009            
--            
-- Description: Source of Report, "RptCSCRExecutedAgreements"            
--                  In Insight DB Reporting Services            
--            
-- Lists all corporate-sponsored clinical research agreements executed             
-- within a specified time period (i.e., 3 months, YTD, annual); distributed             
-- quarterly to MGH/BWH/PHS VPs and department chiefs and ad hoc upon request             
-- by senior leadership.            
--            
-- Parameters:              
--             @StartDate          Date of Status "CSR Executed"            
--             @EndDate                        
--             @CSCRCaseManager                     
--             @Institution                 
--             @Department            
--             @Unit              
--      @AgreeType             
--         @IncludeDanaFarber           
--      @DomainUserId            
--            
-- Returns:     INT, 0=Success, -1=Error            
--            
-- SampleCall:            
-- EXECUTE dbo.RptCSCRExecutedAgreements()          
--            
--------------------------------------------------------------------------            
-- Date        Initials  Modification            
--------------------------------------------------------------------------            
-- 08/13/09     PC        Created            
--            
-- =======================================================================            
    


                           
CREATE  PROCEDURE [dbo].[RptCSCRExecutedAgreements]            
     (            
     @StartDate                         DATETIME,              
     @EndDate                           DATETIME,            
     @CSCRCaseManager                   VARCHAR(100) = NULL,            
     @Institution                       VARCHAR(100) = NULL,                         
     @Department                        VARCHAR(100) = NULL,            
     @Unit                              VARCHAR(100) = NULL,           
     @AgreeType                         VARCHAR(100) = NULL,    
     @IncludeDanaFarber                 VARCHAR(3) = NULL,            
     @DomainUserId                      INT            
     )             
                                               
AS            
BEGIN            
     -- Initial Settings            
     SET NOCOUNT ON            
                 
     -- Declare Local Variables            
     DECLARE   @Error INT,            
               @RowCount INT,            
               @ConversionDate1 DATETIME,            
               @ConversionDate2 DATETIME,            
               @ReturnCode         INT,            
      @CDA VARCHAR(10),             
      @CTA VARCHAR(10),     
      @Amendments VARCHAR(10),    
      @Subcontracts VARCHAR(10),    
      @Other VARCHAR(10)    
            
     -- Default this, Records with these PropStatDate(s) will not be returned            
     SET  @ConversionDate1 = '8/3/2007'            
     SET  @ConversionDate2 = '8/4/2007'           
          
                
  SET @CDA = NULL              
  SET @CTA = NULL              
  SET @Amendments = NULL              
  SET @Subcontracts = NULL              
  SET @Other = NULL              
           
      
             
    IF @AgreeType = 'CDA' SET @CDA = 'DATA'              
    IF @AgreeType = 'CTA' SET @CTA = 'DATA'              
    IF @AgreeType = 'Amendments' SET @Amendments = 'DATA'              
    IF @AgreeType = 'Subcontracts' SET @Subcontracts = 'DATA'              
    IF @AgreeType = 'Other' SET @Other = 'DATA'            
           
                           
     -- ALL checking            
     IF @CSCRCaseManager IN ('ALL','','0') SET @CSCRCaseManager = NULL            
     IF @Institution IN ('ALL','','0') SET @Institution = NULL            
     IF @Department IN ('ALL','','0') SET @Department = 'ALL'            
     IF @Unit IN ('ALL','','0') SET @Unit = 'ALL'            
     IF @IncludeDanaFarber IN ('Yes','1') SET @IncludeDanaFarber = NULL ELSE SET @IncludeDanaFarber = '2'            
        
            
 DECLARE   @Agreements1 TABLE (            
                    PrincipalInvestigator    VARCHAR(100)    NULL,            
     ProposalTitle    VARCHAR(800)    NULL,            
     PrimarySponsor    VARCHAR(200)    NULL,            
     SubActivityType    VARCHAR(100)    NULL,            
     ApprovalDate             DATETIME       NULL,            
     SecondarySponsor         VARCHAR(100)    NULL,            
     SubAgreementType   VARCHAR(100)    NULL,            
     InPatientCareTotal   MONEY          NULL,            
     OneTimeFees     MONEY          NULL,            
     ExpectedPatientEnrollment MONEY          NULL,            
     FARate      MONEY          NULL,            
     ProjectDirectCost   MONEY          NULL,             
     PropBudStat     VARCHAR(100)    NULL,            
     PropBudStatDate    DATETIME       NULL,            
     PropStat     VARCHAR(300)    NULL,            
     PropStatDate    DATETIME       NULL,            
     FullyExecutedAwardedDate DATETIME       NULL,            
     CSCRCaseManager    VARCHAR(100)    NULL,            
     CSCRCaseManagerName   VARCHAR(100)    NULL,            
     AgreementType    VARCHAR(100)    NULL,            
     TransactionType    VARCHAR(100)    NULL,            
     ProposalType    VARCHAR(100)    NULL,            
     Institution     VARCHAR(100)    NULL,            
     Department     VARCHAR(100)    NULL,            
     ChiefCode                VARCHAR(4)     NULL,            
     UnitCode                 VARCHAR(100)    NULL,            
     ProposalStatus    VARCHAR(100)    NULL,            
     InfoEdPropNumber         VARCHAR(100)    NULL,            
     PrincipalInvestigatorId  INT            NULL,            
     SponsorId     VARCHAR(100)    NULL,            
     AgrResourceId    VARCHAR(100)    NULL,            
     FolderNumber             VARCHAR(20)    NULL,            
     AmendmentFolderNumber    VARCHAR(20)    NULL,            
     AgreementId              INT            NULL,            
     AmendFirstPropStatChgDate DATETIME       NULL,            
     MstrFirstPropStatChgDate DATETIME       NULL,            
     LegFEAPDate     DATETIME       NULL,            
     SubContractName    VARCHAR(100)    NULL,            
     SubContractTranType   VARCHAR(100)    NULL,            
     SubContractStatus   VARCHAR(100)    NULL,            
     SubContractStatusDate  DATETIME       NULL,            
     InstitutionId            INT            NULL,            
     DepartmentId             INT            NULL,            
     UnitId                   INT            NULL)            
                      
     -- Let Detail Proc do the work.            
     INSERT    @Agreements1            
     (PrincipalInvestigator,            
     ProposalTitle,            
     PrimarySponsor,            
     SubActivityType,            
     ApprovalDate,            
     SecondarySponsor,            
     SubAgreementType,            
     InPatientCareTotal,            
     OneTimeFees,            
     ExpectedPatientEnrollment,            
     FARate,            
     ProjectDirectCost,             
     PropBudStat,            
     PropBudStatDate,            
     PropStat,            
     PropStatDate,            
     FullyExecutedAwardedDate,            
     CSCRCaseManager,            
     CSCRCaseManagerName,            
     AgreementType,            
     TransactionType,            
     ProposalType,            
     Institution,            
     Department,            
     ChiefCode,            
     UnitCode,            
     ProposalStatus,            
     InfoEdPropNumber,            
     PrincipalInvestigatorId,            
     SponsorId,            
     AgrResourceId,            
     FolderNumber,            
     AmendmentFolderNumber,            
                    AgreementId,            
     AmendFirstPropStatChgDate,            
     MstrFirstPropStatChgDate,            
     LegFEAPDate,            
     SubContractName,            
     SubContractTranType,            
     SubContractStatus,            
     SubContractStatusDate,            
        InstitutionId,            
        DepartmentId,            
                UnitId)            
            
 EXECUTE @ReturnCode = dbo.RptCSCRExecutedAgreementsDetails            
                              @StartDate               = @StartDate,            
                              @EndDate                 = @EndDate,            
         @CSCRCaseManager     = @CSCRCaseManager,             
         @Institution             = @Institution,            
         @Department              = @Department,            
         @Unit                    = @Unit,                     
         @IncludeDanaFarber    = @IncludeDanaFarber,            
         @DomainUserId            = @DomainUserId            

                                          
  -- check for errors            
     SELECT @Error=@@ERROR            
     IF @Error != 0 OR @ReturnCode != 0            
     BEGIN            
          RETURN -1            
     END            
            
            
                 
     -- Return Result Set            
     SELECT    PrincipalInvestigator,            
               ProposalTitle,                            
               PrimarySponsor,            
               SubActivityType,            
               ApprovalDate,            
               SecondarySponsor,            
               SubAgreementType,                      
               InPatientCareTotal,            
               OneTimeFees,            
               ExpectedPatientEnrollment,            
               FARate,            
               ProjectDirectCost,            
               PropBudStat,            
               PropBudStatDate,            
               PropStat,      
               PropStatDate,            
               FullyExecutedAwardedDate,            
               CSCRCaseManager,            
               CSCRCaseManagerName,            
               AgreementType,            
               TransactionType,            
               ProposalType,            
               Institution,            
               Department,            
               ChiefCode,            
               UnitCode,            
               ProposalStatus,            
               InfoEdPropNumber,            
               PrincipalInvestigatorId,            
               SponsorId,            
               AgrResourceId,            
               FolderNumber,            
               AmendmentFolderNumber,            
               AgreementId,            
               AmendFirstPropStatChgDate,            
               MstrFirstPropStatChgDate,            
               LegFEAPDate,            
               SubContractName,            
               SubContractTranType,            
               SubContractStatus,            
               SubContractStatusDate,          
               InstitutionId,               
               DepartmentId,    
               UnitId     
     FROM @Agreements1 AS A              
     WHERE           
      (@CDA IS NULL OR LEFT(A.SubActivityType,3)= 'CDA') AND              
      (@CTA IS NULL OR LEFT(A.SubActivityType,5)= 'Trial') AND                    
      (@Amendments IS NULL OR A.TransactionType IN ('CSCR-Amendment')) AND -- = 'CSCR-Amendment') AND               
      (@Subcontracts IS NULL OR A.SubContractTranType IN ('CSCR - Subcontract')) AND --= 'CSCR - Subcontract') AND
      (@Other IS NULL OR (LEFT(A.SubActivityType,3) != 'CDA' AND LEFT(A.SubActivityType,5) != 'Trial'))          
             
            
SELECT @Error=@@ERROR,@RowCount=@@ROWCOUNT            
     IF @Error!=0            
          RETURN -1            
                      
     -- Return Success            
     RETURN 0            
            
                 
END 
GO

IF OBJECT_ID('dbo.RptCSCRExecutedAgreements') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE dbo.RptCSCRExecutedAgreements >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.RptCSCRExecutedAgreements >>>'
go

GRANT EXECUTE ON dbo.RptCSCRExecutedAgreements TO InsightReport,Insight
go


